/* 
 * Enterprise Library Extensions - DI + ORM.
 * Copyright (C) 2006
 *
 * GNU General Public License
 *
 * This program is free software; you can redistribute 
 * it and/or modify it under the terms of the GNU General Public License 
 * as published by the Free Software Foundation; either version 2 of the 
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful, but 
 * WITHOUT ANY WARRANTY; without even the implied warranty of 
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
 * See the GNU General Public License for more details.
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using ELE.EnterpriseLibrary.Properties;

namespace ELE.EnterpriseLibrary.Data
{
  /// <summary>
  /// A generic implementation of <see cref="ISqlQueryBuilder"/>.
  /// </summary>
  public class GenericSqlQueryBuilder : ISqlQueryBuilder
  {
    private ISqlParser genericParser;
    /// <summary>
    /// Default constructor that initializes a <see cref="GenericSqlParser"/> and facilitates creation of this object using reflection.
    /// </summary>
    public GenericSqlQueryBuilder()
    {
      genericParser = new GenericSqlParser();
    }
    /// <summary>
    /// A helper method that builds a select parts of a select sql statement based ob a sql mapping
    /// </summary>
    /// <param name="queryObject">The sql mapping data</param>
    /// <returns>Part of the select statement built with the provided queryObject</returns>
    protected string BuildSelectString(ObjectSqlMapping queryObject)
    {
      StringBuilder sql = new StringBuilder();
      if (queryObject != null && queryObject.Tables != null)
      {
        foreach (ObjectSqlMappingTable table in queryObject.Tables)
        {
          foreach (ObjectPropertySqlBinding binding in table.Bindings)
          {
            if (!binding.IsCollection)
            {
              string tableName = table.Name;
              sql.Append(tableName).Append(".");
              sql.Append(binding.ColumnName).Append(" as ").Append(binding.OutputFieldName).Append(", ");
            }
          }
        }
        sql.Remove(sql.Length - 2, 2);
      }
      return sql.ToString();
    }
    /// <summary>
    /// A helper method that builds a portion of a sql statement that defines tables to select from.
    /// </summary>
    /// <param name="queryObject"></param>
    /// <returns></returns>
    protected string BuildFromString(ObjectSqlMapping queryObject)
    {
      StringBuilder fromStr = new StringBuilder();
      if (queryObject != null && queryObject.Tables != null)
      {
        foreach (ObjectSqlMappingTable table in queryObject.Tables)
        {
          fromStr.Append(table.Name).Append(", ");
        }
        fromStr.Remove(fromStr.Length - 2, 2);
      }
      return fromStr.ToString();
    }
    /// <summary>
    /// Helper method that builds a part of a sql statement for the where clause portion.
    /// </summary>
    /// <param name="queryObject"></param>
    /// <param name="propertyBinding"></param>
    /// <param name="sqlOperator"></param>
    /// <returns></returns>
    protected string BuildWhereString(ObjectSqlMapping queryObject, ObjectPropertySqlBinding propertyBinding, string sqlOperator)
    {
      StringBuilder whereStr = new StringBuilder();
      if (queryObject != null && propertyBinding != null)
      {
        string pkTabName = null;
        if (!string.IsNullOrEmpty(propertyBinding.TableName))
        {
          pkTabName = propertyBinding.TableName;
        }
        else
        {
          pkTabName = queryObject.PrimaryTableName;
        }
        whereStr.Append(pkTabName).Append(".").Append(propertyBinding.ColumnName).Append(" ").Append(sqlOperator).Append(" ");
        whereStr.Append(this.SqlParser.BuildInputParameterName(propertyBinding.InputFieldName));
      }
      return whereStr.ToString();
    }
    /// <summary>
    /// Helper method that build a portion of sql statement that represents a joint to  foreign key.
    /// </summary>
    /// <param name="queryObject"></param>
    /// <param name="foreignKeyBinding"></param>
    /// <param name="sqlOperator"></param>
    /// <returns></returns>
    protected string BuildForeignKeyWhereString(ObjectSqlMapping queryObject, 
                                                ObjectPropertySqlBinding foreignKeyBinding, 
                                                string sqlOperator)
    {
      StringBuilder whereStr = new StringBuilder();
      if (queryObject != null && foreignKeyBinding != null)
      {
        string pkTabName = null;
        if (string.IsNullOrEmpty(foreignKeyBinding.JointTable))
        {
          pkTabName = foreignKeyBinding.SubQuery.GetPrimaryTable().Name;
        }
        else
        {
          pkTabName = foreignKeyBinding.JointTable;
        }
        whereStr.Append(pkTabName).Append(".").Append(foreignKeyBinding.JointColumn).Append(" ").Append(sqlOperator).Append(" ");
        whereStr.Append(this.SqlParser.BuildInputParameterName(queryObject.PrimaryKeyBinding.InputFieldName));
        whereStr.Append(" and ").Append(queryObject.GetPrimaryTable().Name).Append(".").Append(queryObject.PrimaryKeyBinding.ColumnName);
        whereStr.Append(" = ").Append(pkTabName).Append(".").Append(foreignKeyBinding.JointColumn);
      }
      return whereStr.ToString();
    }
    /// <summary>
    /// A helper method that builds a joint clause of a sql statement.
    /// </summary>
    /// <param name="queryObject"></param>
    /// <param name="propertyBinding"></param>
    /// <returns></returns>
    protected string BuildJointString(ObjectSqlMapping queryObject, ObjectPropertySqlBinding propertyBinding)
    {
      StringBuilder jointStr = new StringBuilder();
      if (queryObject != null &&
          propertyBinding != null &&
          !string.IsNullOrEmpty(propertyBinding.TableName)           
          )
      {
        ObjectSqlMappingTable jointTable = null;
        if (string.IsNullOrEmpty(propertyBinding.JointTable))
        {
          jointTable = queryObject.GetPrimaryTable();
        }
        else
        {
          jointTable = queryObject.GetTable(propertyBinding.JointTable);
        }
        if(jointTable.Name != propertyBinding.TableName)
        {
          jointStr.Append(queryObject.PrimaryTableName).Append(".").Append(propertyBinding.JointColumn).Append(" = ");
          jointStr.Append(propertyBinding.TableName).Append(".").Append(propertyBinding.ColumnName).Append(" ");
        }
      }
      return jointStr.ToString();
    }
    /// <summary>
    /// Not implemented yet! Should return an order by portion of a sql statement.
    /// </summary>
    /// <param name="queryObject"></param>
    /// <returns></returns>
    protected string BuildOrderByString(ObjectSqlMapping queryObject)
    {
      return "";
    }
    /// <summary>
    /// Builds an entire sql statement for a select by property binding.
    /// </summary>
    /// <param name="queryObject"></param>
    /// <param name="propertyBinding"></param>
    /// <returns></returns>
    protected string BuildSqlQueryStringByProperty(ObjectSqlMapping queryObject, ObjectPropertySqlBinding propertyBinding)
    {
      StringBuilder sql = new StringBuilder("select ");
      if (propertyBinding.IsCollection)
      {
        sql.Append(this.BuildSelectString(propertyBinding.SubQuery));
        sql.Append(" from ").Append(this.BuildFromString(propertyBinding.SubQuery));
        sql.Append(", ").Append(queryObject.PrimaryTableName);
        sql.Append(" where ").Append(this.BuildForeignKeyWhereString(queryObject, propertyBinding, "="));
        foreach (ObjectSqlMappingTable table in propertyBinding.SubQuery.Tables)
        {
          if (table.Name != queryObject.PrimaryTableName)
          {
            foreach (ObjectPropertySqlBinding jointBinding in table.Bindings)
            {
              string jointString = this.BuildJointString(propertyBinding.SubQuery, jointBinding);
              if (!string.IsNullOrEmpty(jointString))
              {
                sql.Append(" and ").Append(jointString);
              }
            }
          }
        }
      }
      else
      {
        sql.Append(this.BuildSelectString(queryObject));
        sql.Append(" from ").Append(this.BuildFromString(queryObject));
        sql.Append(" where ").Append(this.BuildWhereString(queryObject, propertyBinding, "="));
        foreach (ObjectSqlMappingTable table in queryObject.Tables)
        {
          if (table.Name != queryObject.PrimaryTableName)
          {
            if (string.IsNullOrEmpty(table.JointColumn))
            {
              throw new System.Configuration.ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionJointColumnNotFound, table.Name, queryObject.MapName, queryObject.Name));
            }
            sql.Append(" and ").Append(table.Name).Append(".").Append(table.JointColumn).Append(" = ");
            sql.Append(queryObject.PrimaryTableName).Append(".").Append(queryObject.PrimaryKeyBinding.ColumnName);
          }
        }
      }
      return sql.ToString();
    }

    #region ISqlQueryBuilder Members
    /// <summary>
    /// Implementation of the interace method.
    /// See <see cref="ISqlQueryBuilder.SqlParser"/> for information.
    /// </summary>
    public virtual ISqlParser SqlParser
    {
      get { return genericParser; }
    }
    /// <summary>
    /// Implementation of the interace method.
    /// See <see cref="ISqlQueryBuilder.BuildSqlQueryString(ObjectSqlMapping, string)"/> for information.
    /// </summary>
    /// <param name="objectMapping"></param>
    /// <param name="bindingName"></param>
    /// <returns></returns>
    public virtual string BuildSqlQueryString(ObjectSqlMapping objectMapping, string bindingName)
    {
      ObjectPropertySqlBinding propertyBinding = objectMapping.GetBinding(bindingName);
      if (propertyBinding == null)
      {
        throw new System.Configuration.ConfigurationErrorsException(string.Format(Resources.Culture, Resources.ExceptionBindingPropertyNotFound, bindingName, objectMapping.MapName, objectMapping.Name));
      }
      return this.BuildSqlQueryStringByProperty(objectMapping, propertyBinding);
    }
    /// <summary>
    /// Implementation of the interace method.
    /// See <see cref="ISqlQueryBuilder.BuildSqlQueryString(ObjectSqlMapping)"/> for information.
    /// </summary>
    /// <param name="objectMapping"></param>
    /// <returns></returns>
    public virtual string BuildSqlQueryString(ObjectSqlMapping objectMapping)
    {
      return this.BuildSqlQueryStringByProperty(objectMapping, objectMapping.PrimaryKeyBinding);
    }
    /// <summary>
    /// Implementation of the interace method.
    /// See <see cref="ISqlQueryBuilder.BuildSqlInsertString"/> for information.
    /// </summary>
    /// <param name="mapping"></param>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public virtual string BuildSqlInsertString(ObjectSqlMapping mapping, string tableName)
    {
      ObjectSqlMappingTable objectTable = mapping.GetTable(tableName);

      StringBuilder insertQuery = new StringBuilder("insert into ");
      insertQuery.Append(objectTable.Name).Append(" ( ");
      StringBuilder columns = new StringBuilder();
      StringBuilder values = new StringBuilder();
      foreach (ObjectPropertySqlBinding binding in objectTable.Bindings)
      {
        if (binding.SubQuery == null || !binding.IsCollection)
        {
          columns.Append(binding.ColumnName).Append(", ");
          values.Append(this.SqlParser.BuildInputParameterName(binding.InputFieldName)).Append(", ");
        }
      }
      if (!objectTable.IsPrimary)
      {
        columns.Append(objectTable.JointColumn).Append(", ");
        values.Append(this.SqlParser.BuildInputParameterName(mapping.PrimaryKeyBinding.InputFieldName)).Append(", ");
      }
      columns.Remove(columns.Length - 2, 2);
      values.Remove(values.Length - 2, 2);
      insertQuery.Append(columns.ToString()).Append(" ) values ( ").Append(values.ToString()).Append(" ) ");
      return insertQuery.ToString();
    }
    /// <summary>
    /// Implementation of the interace method.
    /// See <see cref="ISqlQueryBuilder.BuildSqlUpdateString"/> for information.
    /// </summary>
    /// <param name="mapping"></param>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public virtual string BuildSqlUpdateString(ObjectSqlMapping mapping, string tableName)
    {
      ObjectSqlMappingTable objectTable = mapping.GetTable(tableName);
      StringBuilder updateQuery = new StringBuilder("update ");
      updateQuery.Append(objectTable.Name).Append(" set  ");
      StringBuilder values = new StringBuilder();
      foreach (ObjectPropertySqlBinding binding in objectTable.Bindings)
      {
        if (!objectTable.IsPrimary || (binding.Name != objectTable.PrimaryKeyBinding.Name))
        {
          if (binding.SubQuery == null || !binding.IsCollection)
          {
            values.Append(binding.ColumnName).Append(" = ").Append(this.SqlParser.BuildInputParameterName(binding.InputFieldName)).Append(", ");
          }
        }
      }
      values.Remove(values.Length - 2, 2);
      updateQuery.Append(values.ToString()).Append(" where ");
      if (objectTable.IsPrimary)
      {
        updateQuery.Append(objectTable.PrimaryKeyBinding.ColumnName).Append(" = ").Append(this.SqlParser.BuildInputParameterName(objectTable.PrimaryKeyBinding.InputFieldName));
      }
      else
      {
        updateQuery.Append(objectTable.JointColumn).Append(" = ").Append(this.SqlParser.BuildInputParameterName(mapping.PrimaryKeyBinding.InputFieldName));
      }
      return updateQuery.ToString();
    }
    /// <summary>
    /// Implementation of the interace method.
    /// See <see cref="ISqlQueryBuilder.BuildSqlDeleteString"/> for information.
    /// </summary>
    /// <param name="mapping"></param>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public virtual string BuildSqlDeleteString(ObjectSqlMapping mapping, string tableName)
    {
      ObjectSqlMappingTable objectTable = mapping.GetTable(tableName);
      StringBuilder deleteQuery = new StringBuilder("delete from ");
      deleteQuery.Append(objectTable.Name).Append(" where  ");
      if (objectTable.IsPrimary)
      {
        deleteQuery.Append(objectTable.PrimaryKeyBinding.ColumnName).Append(" = ").Append(this.SqlParser.BuildInputParameterName(objectTable.PrimaryKeyBinding.InputFieldName));
      }
      else
      {
        deleteQuery.Append(objectTable.JointColumn).Append(" = ").Append(this.SqlParser.BuildInputParameterName(mapping.PrimaryKeyBinding.InputFieldName));
      }
      return deleteQuery.ToString();
    }
    /// <summary>
    /// Implementation of the interace method.
    /// See <see cref="ISqlQueryBuilder.CastObjectFromReader"/> for information.
    /// </summary>
    /// <param name="reader"></param>
    /// <param name="index"></param>
    /// <param name="sqlType"></param>
    /// <param name="objectType"></param>
    /// <returns></returns>
    public virtual object CastObjectFromReader(IDataReader reader, int index, string sqlType, Type objectType)
    {
      object o = reader.GetValue(index);
      if (o == DBNull.Value)
      {
        return null;
      }
      return Convert.ChangeType(o, objectType);
    }
    #endregion
  }
}
